from pymysql import Connect

client = Connect(
    host='127.0.0.1',
    port=3306,
    user='admin',
    password='qwe123',
    charset='utf8'
)
print(client)

# 创建游标
cursor_sql = client.cursor()



from random import randint
class Bank:
    def __init__(self):
        pass

    def create_database(self):
        create_database_sql = 'create database if not exists Bank;'
        cursor_sql.execute(create_database_sql)

    def use_database(self,database):
        use_sql = f'use {database};'
        cursor_sql.execute(use_sql)

    def create_table(self):
        create_table_sql = 'create table if not exists bank_user(' \
                           'card_id bigint primary key auto_increment,' \
                           'card_name varchar(30) not null,' \
                           'balance float not null default 0,' \
                           'password int not null default 123456);'
        cursor_sql.execute(create_table_sql)


    def insert_data(self, name, balance=None, password=None):
        card_id = int(f'{randint(100, 900)}{randint(300, 800)}{randint(1000, 9999)}')
        try:
            if password and balance:
                insert_sql = 'insert into bank_user value (%s,%s,%s,%s)'
                cursor_sql.execute(insert_sql, (card_id, name, balance, password))
            else:
                insert_sql = 'insert into bank_user(card_id,card_name) value (%s,%s)'
                cursor_sql.execute(insert_sql, (card_id, name))

        except Exception:
            client.rollback()

        else:
            client.commit()


    def update_data(self,table,update_col,update_data,card_id):
        update_sql = f'update {table} set {update_col}={update_data} where card_id={card_id}'
        try:
            cursor_sql.execute(update_sql)
        except Exception:
            client.rollback()
        else:
            client.commit()

    def delete_data(self,table_user,card_id,password):
        delete_sql = f'delete from {table_user} where card_id={card_id} and password={password}'
        try:
            cursor_sql.execute(delete_sql)
        except Exception:
            client.rollback()
        else:
            client.commit()

data_text =Bank()
data_text.create_database()
data_text.use_database('Bank')
data_text.create_table()
# data_text.insert_data('李想')
data_text.insert_data('张三')